<?php
abstract class AbsMySqlDao {

    protected $binds;

    protected $tableName;

    protected $connection;

    protected $pk;

    private static $dataTypeTable = array(
    'TINYINT'=>'i',
		'SMALLINT'=>'i',
		'MEDIUMINT'=>'i',
		'INT'=>'i',
		'INTEGER'=>'i',
		'BIGINT'=>'i',
		'FLOAT'=>'d',
		'DOUBLE'=>'d',
		'REAL'=>'d',
		'DECIMAL'=>'d',
		'NUMERIC'=>'d',
        'DATE'=>'s'
    );

    final public function getBindType($dbType){
        $dbType = strtoupper($dbType);
        if(array_key_exists($dbType, self::$dataTypeTable)){
            return  self::$dataTypeTable[$dbType];
        }
        else{
            return 's';
        }
    }

    final public function setConnection($conn) {
        $this->connection = $conn;
        //DebugUtil::dump($this->connection, __FILE__, __LINE__);
    }

    /**
     * 向資料庫新增一筆新記錄.
     *
     * @param ValueObject $vo - Active Record物件
     * @return bool/int
     */
    public function create(ValueObject $vo) {
        $this->_checkVars();

        $colNames = array();
        $binds = array();
        $markers = array();

        foreach ($this->binds as $field => $fieldObj) {
            if ($fieldObj->isReal
            && (!(array_key_exists($field, $this->pk) && $fieldObj->ai))
            && isset($vo->$field)) {
                $colNames[] = $fieldObj->name;
                $binds[$field] = $vo->$field;
                $markers[] = '?'; //placeholder
            }
        }

        $fieldsStr = implode(',', $colNames);
        $markersStr = implode(',', $markers);

        $sql = "INSERT INTO {$this->tableName} ($fieldsStr) VALUES ({$markersStr})";
        return $this->_execute($sql, $binds, true);
    }

    /**
     * 修改資料庫中已存在的一筆資料.
     *
     * @param ValueObject $vo
     * @return bool
     */
    public function update(ValueObject $vo) {
        $this->_checkVars();
        $updateStr = '';
        $binds = array();

        foreach ($this->binds as $field => $fieldObj) {
            if ($fieldObj->isReal
            && (!(array_key_exists($field, $this->pk)))
            && isset($vo->$field)){
                $updateStr .= "{$fieldObj->name} = ?,";
                $binds[$field] = $vo->$field;
            }
        }
        //去掉最後一個多餘的","
        if ($updateStr != ''){
            $updateStr = substr($updateStr,0,-1);
        }

        $where = " 1=1 ";
        foreach($this->pk as $field => $fieldObj){
            $where .= " AND {$fieldObj->name} = ?";
            $binds[$field] = $vo->$field;
            //            $fields[] = $field;
            //           $values[] = $vo->$field;
        }
        $sql = "UPDATE {$this->tableName} SET {$updateStr} WHERE $where";
        return $this->_execute($sql, $binds);
    }

    /**
     * 刪除資料庫中已存在的一筆資料.
     *
     * @param ValueObject $vo
     * @return bool
     */
    public function delete(ValueObject $vo) {
        $this->_checkVars();
        $values = array();
        $where = " 1=1 ";
        $binds = array();
        foreach($this->pk as $field => $fieldObj){
            $where .= " AND {$fieldObj->name} = ?";
            $binds[$field] = $vo->$field;
        }

        $sql = "DELETE FROM {$this->tableName} WHERE $where";
        return $this->_execute($sql, $binds);
    }


    /**
     * 查詢資料庫,返回VO
     *
     * @param string $condition - 查詢條件
     * @param array  $values    - 綁定值
     * @return array
     */
    public function query($condition='', $binds = array()) {
        $this->_checkVars();
        $where = empty($condition) ? '' : (' WHERE ' . $condition);
        $sql = "SELECT * FROM {$this->tableName} $where";
        $rs = $this->_fetch($sql, $binds);
//        DebugUtil::dump($rs, __FILE__, __LINE__);

        // convert to ValueObjects
        $vos = array();
        foreach($rs as $row){
            $vos[] = $this->getVo($row);
        }
        return $vos;

    }


    /**
     * 根據指定條件返回記錄總數.
     *
     * @param string $where
     * @return int
     */
    public function getCount($where = '', $bind=array()) {
        $this->_checkVars();
        $where = empty($where) ? '' : (' WHERE ' . $where);
        $sql = "SELECT COUNT(1) AS TOTAL FROM {$this->tableName} " . $where;
        $rs = $this->_fetch($sql, $bind);
        if (count($rs) == 0) {
            return false;
        }
        if (isset($rs[0]['TOTAL'])) {
            return $rs[0]['TOTAL'];
        } else {
            return false;
        }
    }
    
    public function getCounts($where = '', $bind=array() , $groupBy=array()) {
        $this->_checkVars();
        $where = empty($where) ? '' : (' WHERE ' . $where);
        $sql = " FROM {$this->tableName} " . $where;
        if(count($groupBy)>0){
            $groupFields  = implode(",", $groupBy);
            $sql = " SELECT $groupFields ,COUNT(1) AS TOTAL $sql group by $groupFields";
        }else{
            $sql = " SELECT COUNT(1) AS TOTAL $sql"; 
        }
        $rs = $this->_fetch($sql, $bind);
        return $rs;
    }
    
    public function getCounts($where = '', $bind=array() , $groupBy=array()) {
        $this->_checkVars();
        $where = empty($where) ? '' : (' WHERE ' . $where);
        $sql = " FROM {$this->tableName} " . $where;
        if(count($groupBy)>0){
            $groupFields  = implode(",", $groupBy);
            $sql = " SELECT $groupFields ,COUNT(1) AS TOTAL $sql group by $groupFields";
        }else{
            $sql = " SELECT COUNT(1) AS TOTAL $sql"; 
        }
        $rs = $this->_fetch($sql, $bind);
        return $rs;
    }

    abstract public function getVo($row);

    /**
     * 檢查必要的資料,如果資料不完整將會抛出異常.
     *
     * @access private
     */
    private function _checkVars()
    {
        $clsName = get_class($this);

        //未指定表欄位時
        if (is_null($this->binds)) {
            throw new SystemException(DAO_ERROR,"No table fields specified, set {$clsName}::binds first.");
        }

        //未指定表主鍵時
        if (is_null($this->pk)) {
            throw new SystemException(DAO_ERROR,"No primary key specified, please set {$clsName}::pk.");
        }

        //未指定表名
        if (is_null($this->tableName)) {
            throw new SystemException(DAO_ERROR,"No table name specified, please set {$clsName}::tableName.");
        }

        //未指定資料庫連接 Resource時
        if (is_null($this->connection)) {
            throw new SystemException(DAO_ERROR,"No database connection specified, please call {$clsName}::setConnection first.");
        }

        if (! $this->connection instanceof mysqli ) {
            throw new SystemException(DAO_ERROR,"Invalid connection");
        }
    }

    /**
     * 執行SQL script.
     *
     * @access private
     * @param string $sql
     * @param array $binds - array({vo.property}=>{value})
     * @param bool $insert - is it a insert command
     * @return bool - SQL執行是否成功.
     */
    protected function _execute($sql, $binds, $insert=false)
    {
        //DebugUtil::dump($values, __FILE__, __LINE__);exit;
        $stmt = $this->connection->prepare($sql);
        if ($stmt === false) {
            throw new SystemException(DAO_ERROR,$this->connection->error);
        }
        $dtype = '';
        /*    foreach($bind as $v) {
         if (is_string($v)) $dtype .= 's';
         else if (is_integer($v)) $dtype .= 'i';
         else $dtype .= 'd';
         }*/
        $values=array();
        foreach($binds as $field => $value) {
            $dtype .= $this->getBindType($this->binds[$field]->type);
            $values[] = $value;
        }

        if (count($values) > 0){
            $succ = call_user_func_array(array($stmt,'bind_param'),array_merge(array($dtype),$values));
        }

        $scuess = $stmt->execute();

        //DebugUtil::dump(mysqli_stmt_sqlstate($stmt), __FILE__, __LINE__);
        //DebugUtil::dump(mysqli_stmt_error($stmt), __FILE__, __LINE__);

        if ($scuess && $insert) {
            $scuess = $this->connection->insert_id;
        }
        $stmt->close();
        return $scuess;
    }

    /**
     * 執行SQL script, 返回Record Set.
     *
     * @access private
     * @param string $sql
     * @param array $binds - array({vo.property}=>{value})
     * @return array/false
     */
    protected function _fetch($sql, $binds)
    {
        $dtype = '';
        $values = array();
        if (is_array($binds)) {
            foreach($binds as $field => $value) {

                if(array_key_exists($field,$this->binds)){
                    $dtype .= $this->getBindType($this->binds[$field]->type);
                }
                else{
                    $dtype .= $this->getBindType('');
                }
                $values[] = $value;
            }
        }
        $stmt = $this->connection->prepare($sql);
        if (false === $stmt) {
            throw new SystemException(DAO_ERROR, $this->connection->error);
        }
        //echo "<hr>";

        //DebugUtil::dump($this->connection->error, __FILE__, __LINE__);
        //DebugUtil::dump($stmt, __FILE__, __LINE__);
        //DebugUtil::dump($stmt->error, __FILE__, __LINE__);
        //DebugUtil::dump($values, __FILE__, __LINE__);
        //執行綁定        
        if (strlen(trim($dtype)) > 0){
            call_user_func_array(array($stmt,'bind_param'),array_merge(array($dtype),$values));
        }

        //執行有誤時返回
        if (!$stmt->execute()) {
            return false;
        }

        $fields = $stmt->result_metadata();
        $rs_arr = $key_arr = $val_arr = array();
        while($field = $fields->fetch_field()) {
            $key_arr[$field->name] = '';
            $val_arr[] = &$key_arr[$field->name];
        }

        call_user_func_array(array(&$stmt,'bind_result'),$val_arr);

        $i = 0;
        $rs_arr = array();
        while ($stmt->fetch()) {
            foreach ($key_arr as $k=>$v){
                $rs_arr[$i][$k] = $v;
            }
            $i++;
        }
        $stmt->close();
        return $rs_arr;
    }

    /**
     * query a page of ValueObjects.
     * @param PageValue $page
     * @return PageValue
     */
    public function getByPage(PageValue $page){
        $where  = ' 1=1 ';
        if(strlen(trim($page->where))>0){
            $where .= ' AND '. $page->where;
        }
        $bind = $page->getBind();
//        DebugUtil::dump($bind, __FILE__, __LINE__);
        $page->rowCount = $this->getCount($where, $bind);
//        DebugUtil::dump($page, __FILE__, __LINE__);
        if($page->rowCount > 0)
        {
            $page->init();

            if (isset($page->order)) {
                $where .= "order by {$page->order} ";
            }
             
            $where .= ' LIMIT ' . ($page->pageId - 1) * $page->pageSize . ', ' . $page->pageSize;

            $page->list = $this->query($where, $bind);
//            DebugUtil::dump($page, __FILE__, __LINE__);
        }else{
            $page->list = array();
        }
        return $page;
    }
}
